﻿CREATE procedure [dbo].[spProductPricing] ( @ProductID int, @ProductPricingClassCode char(1) = null, @PromotionID int = null )
as
begin
	set nocount on
	declare @resultSet table ( ProductPricingID int, ProductID int, ProductPricingClassCode char(1), PromotionID int, InheritedFrom int )

	insert into @resultSet (  ProductPricingID, ProductID, ProductPricingClassCode, PromotionID, InheritedFrom )
		select ProductPricingID, ProductID, ProductPricingClassCode, PromotionID, null
		from tblProductPricing
		where ProductID = @ProductID
			and coalesce( ProductPricingClassCode, '') = coalesce(@ProductPricingClassCode, ProductPricingClassCode, '')
			and coalesce( PromotionID, -1 ) = coalesce( @PromotionID, PromotionID, -1 )

	declare @IsAProductID int
	set @IsAProductID = null
	select @IsAProductID = IsAProductID from tblProduct where ProductID = @ProductID
	while @IsAProductID is not null
	begin
		insert into @resultSet ( ProductPricingID, ProductID, ProductPricingClassCode, PromotionID, InheritedFrom )
			select T1.ProductPricingID, T1.ProductID, T1.ProductPricingClassCode, T1.PromotionID, @IsAProductID
			from tblProductPricing T1
				left outer join @resultSet T2 on T1.ProductPricingID = T2.ProductPricingID
				left outer join @resultSet T3 on coalesce( T1.ProductPricingClassCode, '') = coalesce(T3.ProductPricingClassCode, '')
					and coalesce( T1.PromotionID, -1 ) = coalesce( T3.PromotionID, -1 )
		where T1.ProductID = @IsAProductID
			and coalesce( T1.ProductPricingClassCode, '') = coalesce(@ProductPricingClassCode, T1.ProductPricingClassCode, '')
			and coalesce( T1.PromotionID, -1 ) = coalesce( @PromotionID, T1.PromotionID, -1 )
			and T2.ProductPricingID is null
			and T3.ProductPricingID is null

		set @IsAProductID = null
		select @IsAProductID = IsAProductID from tblProduct where ProductID = @IsAProductID
	end

	select T3.*, 
		T2.Price,
		T2.ProductPricingClassCode, 
		T2.PromotionID, 
		T4.Description as ProductPricingClassDescription, 
		T5.Description as PromotionDescription,
		T6.ProductID as InheritedFrom_ProductID,
		T6.ItemCode as InheritedFrom_ItemCode,
		T6.ShortName as InheritedFrom_ShortName
	from @resultSet T1
		join tblProductPricing T2 on T1.ProductPricingID = T2.ProductPricingID
		join tblProduct T3 on T1.ProductID = T3.ProductID
		left outer join codeProductPricingClass T4 on T2.ProductPricingClassCode = T4.ProductPricingClassCode
		left outer join tblPromotion T5 on T2.PromotionID = T5.PromotionID
		left outer join tblProduct T6 on T1.InheritedFrom = T6.ProductID
end
